（杂）MySQL 时间类型的思量

发表于 | 更新于 | 分类于 | 阅读次数：
本文字数： 2.1k | 阅读时长 ≈ 3 分钟

DATETIME VS TIMESTAMP

  两者均为时间类型字段，格式都一致，主要有以下四点区别：

  • 时区影响不同（若应用场景有跨时区要求需特别注意这点）：
    • datetime 保存的是绝对值，没有时区，不会变化（若知道存入时的时区，取出时转换可解决跨时区问题，否则会丢失时区信息）
    • timestamp 会跟随设置的时区变化而变化（因为其内部存储的时间戳是不会变的，只是显示随时区变化而已，即其值存储时从当前时区转换为 UTC 存储，检索时从 UTC 转换回当前时区）
  • 时间范围表示的不同：
    • timestamp 时间范围较小：1970-01-01 00:00:00 ~ 2038-01-09 03:14:07
    • datetime 支持的时间范围更广 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
  • 存储空间占用不同：timestamp 储存占用 4 个字节，datetime 储存占用 8 个字节
  • 索引速度不同：timestamp 更轻量，索引比 datetime 更快（其实速度上差别不是很大）

如何选择时间类型？

  基于前面的分析：

  • 考虑存储空间的情况下，timestamp 是最优的时间存储类型
  • 不考虑存储空间的情况下，datetime 是最优的时间存储类型
  • 当然，还要看公司有没有国际化的业务场景，有的话需要注意时区问题

为什么有人 MySQL 时间类型选择 unsigned int ，而不是 timestamp?

  长久以来，MySQL 数据库里的时间戳都是用的 int(10) unsigned 类型，后来看了些别人的代码，发现用 timestamp 好像也蛮不错的。
  MySQL的 timestamp 类型内部其实本质是用的一个 int 整数来存储的从自 UTC 1970 年 1 月 1 日至今的秒数，所以无论是索引性能还是存储空间都没什么区别，MySQL 文档也有说明，当使用select from _unixtime(timestamp)时，是直接访问的内部秒数，而不是格式化后再反格式化。

  尽管大体一样，而 timestamp 类型带来了两点明显的好处：

  • SELECT出来的结果直接就是可理解的
  • 时间字段可以使用CURRENT_TIMESTAMP函数来设置默认值
  • MySQL 及其他系统管理器提供了许多处理日期时间信息的函数，这些函数比自定义函数计算更快

  对于js代码中，new Date(timestamp * 1000)这种乘以1000的写法，总是让人遗忘，而数据库返回的结果直接就是格式化好的，js就只需要做截断或者字符替换操作，不容易出错得多。

  于是我将系统全更换成 timestamp 类型了，然而好景不长，我又全折腾回int unsigned了，因为面对它所带来的两大坏处，它带来的两点好处显得微不足道了：

  • 可能的性能问题
  • 绕不过去的时区问题

可能的性能问题

  默认情况下 MySQL 的时区变量取值为 SYSTEM，即跟随服务器系统的时区：

1
2
3
4
5
6
select @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| SYSTEM      |
+-------------+

  这意味着每次SELECT的数据，都需要查询一下系统的时区设置，然后进行格式化，据说查询的过程还要获取全局锁，这就不仅有 CPU 的计算耗时问题，还会有阻塞问题。有建议的做法是设置@time_zone='+8'来避免频繁对系统时区的查询，但很显然，在布署新环境时，有可能会忘记这件事。

时区问题

  时区问题，我想了好久，也无法绕过去。
  中国时区是 CST，也就是 UTC+8（GMT+8），若应用只针对中国用户，那使用 CST 时区没有问题，但是若跨了时区，就必须存储 UTC 时间，然后各个客户端拿到这一绝对时间秒数后再转换成当地时区所对应的时间，特别像是美国及很多国家，不仅有时区问题，还有夏令时问题，这就更要依靠客户端的时区设置了。

  其实 timestamp 内部的 int 也是使用的 UTC 绝对秒数，但是在SELECT的时候，会转换成服务器所在时区的时间，这时问题就麻烦了，客户端拿到时间之后，正确的做法是先按服务器所在时区反格式化为绝对秒数，再根据当前时区设置格式化对应时间，这显然非常麻烦。

解决办法

  如果涉及到时区问题，最好的方案就是统一 UTC ，展示转换时区即可，即

  • 不要使用 TIMESTAMP，统一用 DATETIME
  • 如果要给 DATATIME 设置默认时间，用函数 UTC_TIMESTAMP() ，不要设置默认值 CURRENT_TIMESTAMP
  • 程序里写入到 DATATIME 中的时间，统一为 UTC ，读取时统一按照 UTC 处理

扩展思考——GMT 和 UTC 有什么区别？

  可以认为 GMT 和 UTC 相等。

0%